Top Selling Product

# Calculate revenue for each product
product_revenue <- orderdetails |>
  inner_join(products, by = "ProductID") |>
  mutate(Revenue = orderdetails$UnitPrice * orderdetails$Quantity * 
           (1 - orderdetails$Discount))

# Identify the top N products by revenue
top_products <- product_revenue |>
  group_by(ProductName) |>
  summarise(TotalRevenue = sum(Revenue)) |>
  arrange(desc(TotalRevenue)) |>
  head(30)

# Display the Top Selling Products using DT::datatable
DT::datatable(
  top_products,
  caption = "Top Selling Products",
  options = list(
    lengthMenu = c(5, 10),
    pageLength = 5,
    searching = TRUE,
    ordering = TRUE
  ),
  style = "bootstrap",
  class = "table table-bordered table-hover"
)

Employee-wise Sales Comparison Using Bar Plot

# Join Employees with Orders and OrderDetails
joined_data <- inner_join(employees, orders, by = "EmployeeID") |>
  inner_join(orderdetails, by = "OrderID")

# Calculate total sales per employee
employee_sales <- joined_data |>
  group_by(EmployeeID, FirstName, LastName) |>
  summarise(TotalSales = sum(UnitPrice * Quantity * (1 - Discount)))

# Create a bar plot for employee-wise sales comparison
ggplot(employee_sales, aes(x = paste(FirstName, LastName), y = TotalSales, fill = paste(FirstName, LastName))) +
  geom_bar(stat = "identity") +
  labs(title = "Employee-wise Sales Comparison", x = "Employee", y = "Total Sales", fill = "Employee Name") +
  scale_fill_manual(values = rainbow(nrow(employee_sales)), name = "Employee Name") +  # Customize the legend label
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Supplier Contribution

# Join orderdetails with products
joined_data <- orderdetails |>
  inner_join(products, by = "ProductID") |>
  inner_join(suppliers, by = "SupplierID")

# Calculate Supplier Revenue
supplier_revenue <- joined_data |>
  mutate(SupplierRevenue = orderdetails$UnitPrice * orderdetails$Quantity * 
           (1 - orderdetails$Discount))

# Group by SupplierName and calculate TotalSupplierRevenue
supplier_contribution <- supplier_revenue |>
  group_by(SupplierID, CompanyName) |>
  summarise(TotalSupplierRevenue = sum(SupplierRevenue))|>
  arrange(desc(TotalSupplierRevenue)) |>
  head(30)


# Display the Supplier Contribution using DT::datatable
DT::datatable(
  supplier_contribution,
  caption = "Supplier Contribution",
  options = list(
    lengthMenu = c(5, 10),
    pageLength = 10,
    searching = TRUE,
    ordering = TRUE
  ),
  style = "bootstrap",
  class = "table table-bordered table-hover"
)

Order Date Analysis

# Convert OrderDate to a Date object
orders$OrderDate <- as.Date(orders$OrderDate, format = "%m/%d/%Y")

# Extract and analyze year, month, and day
orders$Year <- format(orders$OrderDate, "%Y")
orders$Month <- format(orders$OrderDate, "%m")
orders$Day <- format(orders$OrderDate, "%d")

# Analysis by Year
orders_by_year <- orders |>
  group_by(Year) %>%
  summarise(TotalOrders = n())

# Analysis by Month
orders_by_month <- orders |>
  group_by(Month) %>%
  summarise(TotalOrders = n())

# Analysis by Day
orders_by_day <- orders |>
  group_by(Day) |>
  summarise(TotalOrders = n())

# Visualize the results
knitr::kable(orders_by_year, caption = "orders_by_year")
orders_by_year
Year TotalOrders
1996 152
1997 408
1998 270 
# Display the results using DT::datatable
DT::datatable(
  orders_by_month,
  caption = "Orders by Month",
  options = list(
    lengthMenu = c(5, 10),
    pageLength = 5,
    searching = TRUE,
    ordering = TRUE
  ),
  style = "bootstrap",
  class = "table table-bordered table-hover"
)
DT::datatable(
  orders_by_day,
  caption = "Orders by Day",
  options = list(
    lengthMenu = c(5, 10),
    pageLength = 5,
    searching = TRUE,
    ordering = TRUE
  ),
  style = "bootstrap",
  class = "table table-bordered table-hover"
)

Customer Distribution by Region

customers |>
  group_by(Region) |>
  summarise(NumCustomers = n()) |>
  plot_ly(labels = ~Region, values = ~NumCustomers, type = 'pie') |>
  layout(title = "Customer Distribution by Region")

Customer-wise Orders and Shipments

# Full join Customers with Orders
full_joined_customers_orders <- full_join(customers, orders, by = "CustomerID")

# Count the number of orders and shipments for each customer
customer_orders_shipments <- full_joined_customers_orders |>
  group_by(CustomerID, CompanyName) |>
  summarise(OrdersCount = n(), ShipmentsCount = sum(!is.na(ShippedDate)))

# Select the top 10 customers based on the number of orders
top_customers_orders <- customer_orders_shipments |>
  arrange(desc(OrdersCount)) |>
  head(10)

# Display the Supplier Contribution
knitr::kable(top_customers_orders, caption = "Customer Orders Shipments")
Customer Orders Shipments
CustomerID CompanyName OrdersCount ShipmentsCount
SAVEA Save-a-lot Markets 31 31
ERNSH Ernst Handel 30 30
QUICK QUICK-Stop 28 28
FOLKO Folk och f HB 19 19
HUNGO Hungry Owl All-Night Grocers 19 19
BERGS Berglunds snabbkp 18 18
HILAA HILARION-Abastos 18 18
RATTC Rattlesnake Canyon Grocery 18 18
BONAP Bon app’ 17 17
FRANK Frankenversand 15 15